package org.excilys.training.ebanking.dao;

import java.util.List;

import org.excilys.training.ebanking.idao.IDaoOperation;
import org.excilys.training.ebanking.model.Operation;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.joda.time.DateMidnight;
import org.joda.time.DateTime;
import org.joda.time.DateTimeConstants;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

@Repository(value = "DaoOperation")
public class DaoOperation extends HibernateDaoSupport implements IDaoOperation {

	private static Logger LOGGER;

	@Autowired
	public DaoOperation(SessionFactory sessionFactory) {
		setSessionFactory(sessionFactory);
	}

	/**
	 * Méthode permettant de récupérer une liste d'opérations, excepté ceux
	 * réalisées par cartes, à partir du numero de compte
	 * <p>
	 *
	 * @param id
	 *            numero de compte
	 *
	 * @return liste des opérations d'un compte
	 */
	@SuppressWarnings("unchecked")
	public List<Operation> getAllNoCarteOperationsByCompte(int id) {

		String query = "FROM Operation o INNER JOIN FETCH o.compte c"
				+ " where c.id=? and o.typeOperation in (1,4,5) "
				+ "order by o.dateOperation DESC";

		return getHibernateTemplate().find(query, id);
	}

	/**
	 * Méthode permettant d'obtenir la liste des opération effectué par carte
	 * ppur un compte donnée
	 * <p>
	 *
	 * @param id
	 *            numero de compte
	 * @return liste d'opérations carte
	 */
	@SuppressWarnings("unchecked")
	public List<Operation> getAllCarteOperationsByCompte(int id) {

		String query = "FROM Operation o INNER JOIN FETCH o.compte c"
				+ " where c.id=? and o.typeOperation in (2,3)"
				+ "order by o.dateOperation DESC";

		return getHibernateTemplate().find(query, id);
	}

	/**
	 * Méthode permetant d'obtenir la liste de montants d'opérations par carte
	 * pour un compte, un mois donnée et une année donnée
	 * <p>
	 *
	 * @param id
	 *            numero de compte
	 * @param mois
	 *            mois de l'année choisie
	 * @param annee
	 *            annee choisie
	 *
	 * @return liste de montants
	 */
	@SuppressWarnings("unchecked")
	public List<Double> getMontantsOperationsCarteByMonth(int id, int mois,
			int annee) {

		DateTime dateLimit2=null;
		DateTime dateLimit = new DateTime(annee, mois, 1, 0, 0, 0, 0);
		if (mois == 12) {
			dateLimit2 = new DateTime(annee + 1, 1, 1, 0, 0, 0, 0);
		} else {
			dateLimit2 = new DateTime(annee, mois + 1, 1, 0, 0, 0, 0);
		}
		String query = "Select sum(o.montant) as m  FROM Operation o INNER JOIN  o.compte "
				+ "WHERE o.compte.id=? and o.dateOperation >? and o.dateOperation<?"
				+ "GROUP BY o.typeOperation.id"
				+ " HAVING o.typeOperation.id in (2,3)";

		return getHibernateTemplate().find(query, id, dateLimit,dateLimit2);
	}

	@SuppressWarnings("unchecked")
	public List<Operation> getSomeNoCarteOperationsByCompte(int id, int mois,
			int year, int numeropremiereoperations, int nombreoperation) {

		DateTime dateLimitInf = new DateTime(year, mois, 1, 0, 0, 0, 0);
		DateTime dateLimitSup = null;
		if (0 < mois && mois < 12) {
			dateLimitSup = new DateTime(year, mois + 1, 1, 0, 0, 0, 0);
		}

		org.hibernate.Criteria criteria = getSession().createCriteria(
				Operation.class);

		criteria.createAlias("compte", "compte")
				.add(Restrictions.in("compte.id", new Integer[] { id }))
				.createAlias("typeOperation", "top")
				.add(Property.forName("top.id").in(new Integer[] { 1, 4, 5 }));

		if (mois == 12) {
			dateLimitSup = new DateTime(year + 1, mois, 1, 0, 0, 0, 0);
		} else if (mois > 12) {
			LOGGER.error("Le mois choisi est strictement supérieur a 12, aucun tri sur cette date ne peux etre effectuer");
		}
		criteria.add(Restrictions.ge("dateOperation", dateLimitInf))
				.add(Restrictions.le("dateOperation", dateLimitSup))
				.addOrder(Order.desc("dateOperation"));
		criteria.setFirstResult(numeropremiereoperations);

		return criteria.setMaxResults(nombreoperation).list();
	}

	@SuppressWarnings("unchecked")
	public List<Operation> getSomeCarteOperationsByCompte(int id, int mois,
			int year, int numeropremiereoperations, int nombreoperation) {

		DateTime dateLimitInf = new DateTime(year, mois, 1, 0, 0, 0, 0);
		DateTime dateLimitSup = null;
		if (0 < mois && mois < 12) {
			dateLimitSup = new DateTime(year, mois + 1, 1, 0, 0, 0, 0);
		}

		org.hibernate.Criteria criteria = getSession().createCriteria(
				Operation.class);

		criteria.createAlias("compte", "compte")
				.add(Restrictions.in("compte.id", new Integer[] { id }))
				.createAlias("typeOperation", "top")
				.add(Restrictions.between("top.id", 2, 3));
		if (mois == 12) {
			dateLimitSup = new DateTime(year + 1, mois, 1, 0, 0, 0, 0);
		} else if (mois > 12) {
			LOGGER.error("Le mois choisi est strictement supérieur a 12, aucun tri sur cette date ne peux etre effectuer");
		}
		criteria.add(Restrictions.ge("dateOperation", dateLimitInf))
				.add(Restrictions.le("dateOperation", dateLimitSup))
				.addOrder(Order.desc("dateOperation"));

		criteria.setFirstResult(numeropremiereoperations);

		return criteria.setMaxResults(nombreoperation).list();
	}

	@SuppressWarnings("unchecked")
	public int getNombreOperationsCard(int id, int mois, int year) {

		DateTime dateLimit = new DateTime(year, mois, 1, 0, 0, 0, 0);
		DateTime dateLimit2 = null;
		if (0 < mois && mois < 12) {
			dateLimit2 = new DateMidnight().withYear(year)
					.withMonthOfYear(mois).plusMonths(1).toDateTime();
		}
		if (mois == 12) {
			dateLimit2 = new DateMidnight().withYear(year)
					.withMonthOfYear(DateTimeConstants.JANUARY)
					.withDayOfMonth(1).plusYears(1).toDateTime();
		}
		String query = "Select count(*) From Operation o "
				+ "where  o.compte.id= ? and o.dateOperation<? and o.dateOperation>? "
				+ "and o.typeOperation.id in (2,3) ";

		List<Long> results = getHibernateTemplate().find(query, id, dateLimit2,
				dateLimit);

		return results.get(0).intValue();
	}

	@SuppressWarnings("unchecked")
	public int getNombreOperationsWithoutCard(int id, int mois, int year) {

		DateTime dateLimit = new DateTime(year, mois, 1, 0, 0, 0, 0);
		DateTime dateLimit2 = null;
		if (0 < mois && mois < 12) {
			dateLimit2 = new DateTime(year, mois + 1, 1, 0, 0, 0, 0);
		}
		if (mois == 12) {
			dateLimit2 = new DateTime(year + 1, 1, 1, 0, 0, 0, 0);
		}
		String query = "Select count(*) From Operation o "
				+ "where o.compte.id= ? and  o.dateOperation<? and o.dateOperation>?"
				+ " and o.typeOperation.id in (1,4,5) ";

		List<Long> results = getHibernateTemplate().find(query, id, dateLimit2,
				dateLimit);

		return results.get(0).intValue();
	}

	/**
	 * Création d'une nouvelle operation en base
	 *
	 * @param operation
	 *            l'Operation à sauvegarder
	 *
	 */

	public void createOperation(Operation operation) {
		getHibernateTemplate().save(operation);
	}

	public void deleteOperation(Operation operation) {
		getHibernateTemplate().delete(operation);
	}

	/**
	 * Methode renvoyant la liste des virements (externe et interne) pour un
	 * compte donné
	 *
	 * @param idCompte
	 *            id du compte
	 * @return Liste d'operations
	 */
	@SuppressWarnings("unchecked")
	public List<Operation> getAllVirement(int idCompte) {
		String query = "FROM Operation o INNER JOIN FETCH o.compte c"
				+ " where c.id=? and o.typeOperation in (4,5)"
				+ "order by o.dateOperation DESC";

		List<Operation> operations = getHibernateTemplate().find(query,
				idCompte);

		return operations;

	}

}
